﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;

namespace DAL
{
    public class TonGiaoDAL
    {
        

        public static List<TonGiaoPublic> LoadTonGiao()
        {
            var tongiao = Common.db.ExecuteStoreQuery<TonGiaoPublic>(@"select * from TonGiao");
            return tongiao.ToList();

            //List<TonGiaoPublic> list = new List<TonGiaoPublic>();
            //var query = from q in Common.db.TonGiaos
            //            select q;
            //foreach (var item in query)
            //{
            //    TonGiaoPublic tg = new TonGiaoPublic(item.MaTG, item.TenTonGiao);
            //    list.Add(tg);
            //}
            //return list;
        }
        public static void InsertTonGiao(TonGiaoPublic _tg)
        {
            TonGiao tg = new TonGiao();
            tg.MaTG = _tg.MaTG;
            tg.TenTonGiao = _tg.TenTonGiao;
            Common.db.AddToTonGiaos(tg);
            Common.db.SaveChanges();
        }
        public static byte LoadMaTG()
        {
            var matg = Common.db.ExecuteStoreQuery<byte>(@"select Max(MaTG) as MaTG from TonGiao");
            return matg.FirstOrDefault();
        }
        public static void UpdateTonGiao(byte MaTG, string TenTonGiao)
        {
            var matg = Common.db.ExecuteStoreQuery<TonGiaoPublic>(@"update TonGiao set TenTonGiao=" + "'" + TenTonGiao + "'" + "where MaTG=" + MaTG);
            Common.db.SaveChanges();
        }
        public static TonGiaoPublic LoadTonGiaoByMaTG(int MaTG)
        {
            var tongiao = Common.db.ExecuteStoreQuery<TonGiaoPublic>(@"select TenTonGiao from TonGiao where MaTG=" + MaTG);
            return tongiao.ToList()[0];
        }
        public static List<TonGiaoPublic> LoadBieuDoTonGiao()
        {
            string chuoi = @"
                        select first.MaTG, first.TenTonGiao, first.SL
                        from (
	                        select top 5 T.MaTG,TenTonGiao,COUNT(N.MaTG) as SL
	                        from TonGiao T join Nhankhau N
	                        on T.MaTG = N.MaTG
	                        group by T.MaTG, TenTonGiao
	                        order by SL desc) first
	
                        union all
	
                        select last.MaTG, last.TenTonGiao, last.SL
                        from (
	                        select cast(0 as tinyint) as MaTG, 'Khác' as TenTonGiao,ISNULL(sum(t2.SL),0) as SL
	                        FROM
	                        (   SELECT ROW_NUMBER() OVER(ORDER BY SL desc) AS row, t1.*
		                        FROM
		                        ( 	select COUNT(N.MaTG) as SL
			                        from TonGiao T left join Nhankhau N
			                        on T.MaTG = N.MaTG
			                        group by T.MaTG
		                        ) t1
	                        ) t2
	                        WHERE t2.row BETWEEN 6 AND 100) last; 
                        ";
            var tg = Common.db.ExecuteStoreQuery<TonGiaoPublic>(chuoi);
            return tg.ToList();
        }
        public static void XoaTonGiao(byte MaTG)
        {

            var tg = Common.db.ExecuteStoreQuery<TonGiaoPublic>("delete from TonGiao where MaTG=" + MaTG);
            Common.db.SaveChanges();
        }
        public static int KiemTraTonGiao(byte MaTG)
        {
            var tg = Common.db.ExecuteStoreQuery<int>("select count(MaTG) from NhanKhau where MaTG=" + MaTG);
            return tg.FirstOrDefault();
        }
    }
}
